clear
set seed 1
import delimited "../Data/malta/bid_info.csv"

gen temp = strpos(v1, "resourceId=")
gen url_id = substr(v1, temp+11, .)
drop temp
destring url_id, replace
gen temp_id = 1 in 1
replace temp_id = temp_id[_n-1] + (1-(url_id ==url_id[_n-1])) if _n>1
replace temp_id = temp_id + 1 if _n>=442 /* consecutive entries with same URL */
replace temp_id = temp_id + 1 if _n >= 5484 /* consecutive entries with same URL */
rename temp_id id

save "../Data/malta/bid_info.dta", replace

clear
import delimited "../Data/malta/list.csv"
gen temp = strpos(v12,"resourceId=")
gen temp2 = strlen(v12)
gen url_id = substr(v12,temp+11,temp2-temp -12)
drop temp temp2
drop v1
duplicates drop
keep url_id v10
rename v10 status
save "../Data/malta/status.dta", replace

import delimited "../Data/malta/auction_info.csv", clear
gen id = _n
foreach x of varlist v1-v33 {
	rename `x' v`x'
}
merge 1:m id using "../Data/malta/bid_info.dta"
rename id temp_id
drop if v2 == "None" & v3 == "None" & v4 == "None" & v5 == "None"
foreach x of varlist v6-v10 {
	replace `x' = substr(`x', 3,.)
	replace `x' = substr(`x', 1, strlen(`x')-2)
}
gen single = 1 if v7 == "Single"
replace v7 = v8 if single == 1 
replace v8 = v9 if single == 1
replace v9 = v10 if single == 1
drop v10
drop if v6 == "N/A"
rename v7 firm_name
rename v8 tender_id
rename v9 bid
destring tender_id, replace
replace bid = "." if bid == "-"
destring bid, replace
drop single
rename v3 Date_of_Publication
rename v4 Opening_Date
rename v5 Closing_Date
rename v6 Envelope

/* Several checks to see that merge is proper */
count if vv28 ~= Date_of_Publication & vv28~= "Date of Publication" & Date_of ~= "" /* make sure merge is proper: the outcome should be 0 */
if r(N)~=0 {
	problem
}
drop vv28
count if vv20 ~= Opening_Date & vv20~= "Opening" & Opening ~= "" /* make sure merge is proper: the outcome should be 0 */
if r(N)~=0 {
	problem
}
drop vv20
count if vv16 ~= Closing_Date & vv16~= "receipt of tender" & Closing ~= "" /* make sure merge is proper: the outcome should be 0 */
if r(N)~=0 {
	problem
}
drop vv16
drop _merge

rename vv1 buyer_name
rename vv2 Published_behalf_of
rename vv3 title
rename vv4 CfT_ID
rename vv5 Evaluation_Method
rename vv6 Description
rename vv7 Procurement_Type
rename vv8 Directive
rename vv9 Procedure
rename vv10 CfT_Involves
rename vv11 CPV_Codes
rename vv12 eAuctions
rename vv13 NUTS_codes
rename vv14 Above_Below
rename vv15 Payment_Options
rename vv17 dispatching
rename vv18 clarification
rename vv19 Upload
rename vv21 Register
rename vv22 Lots
rename vv23 Num_Lots
rename vv24 Tenders_Lots
rename vv25 Max_Lots
rename vv26 EU_funding
rename vv27 Multiple_tenders
rename vv29 TED
rename vv30 Date_Awarding
rename vv31 GPP
rename vv32 Certification
replace firm_name = strtrim(firm_name)
egen id = group(temp_id Envelope)



gen double Opening_Date_n = clock(Opening_Date, "DMYhm")
format Opening_Date_n %tc
cap drop temp
gen temp = substr(Opening_Date,1,10)
gen Opening_Date_d = date(temp, "DMY")

merge m:1 url_id using "../Data/malta/pdf_info.dta"
drop _merge
tostring url_id, replace
merge m:1 url_id using "../Data/malta/status.dta" 


/* drop if same firm bids same amount in same auction */
bys id firm_name bid: gen xxx = _n
drop if xxx>1
drop xxx
drop if bid == .


* name and bid match
forvalues x  = 1/37 {
	matchit firm_name winner`x', gen(matchval`x')
}


forvalues x  = 1/37 {
	destring winbid`x', replace force
}

forvalues x = 1/37 {
	replace matchval`x' = 1 if firm_name == "EXCEL SIÌS ENERJIÌ ÃRETIÌM CONSTRUCTION" & winner`x' == "EXCEL SIÌ‡S ENERJIÌ‡ ÃœRETIÌ‡M CONSTRUCTION"
	replace matchval`x' = 1 if firm_name == "Orme Scientific Ltd" & winner`x' == "Orme Scientiï¬c Ltd"
	replace matchval`x' = 1 if firm_name == "E&L Enterprises Ltd" & winner`x' == "E and L Enterprises Ltd"
	replace matchval`x' = 1 if firm_name == "Xerri's Landscaping Co. Ltd" & winner`x' == "Xerri's Landscaping"
	replace matchval`x' = 1 if firm_name == "P and D Pharmaceuticals Limited" & winner`x' == "Health House Pharma Ltd T/as P and D Pharmaceuticals"
	replace matchval`x' = 1 if firm_name == "Cieffe Projects Ltd" & winner`x' == "Cieï¬€e Projects Ltd"
	replace matchval`x' = 1 if firm_name == "S.I.C.T (Ltd)" & winner`x' == "S.I.C.T Ltd"
	replace matchval`x' = 1 if firm_name == "Training Malta" & winner`x' == "AcrossLimits Ltd / Training Malta"
	replace matchval`x' = 1 if firm_name == "Office Essentials Ltd." & winner`x' == "Oï¬ƒce Essentials Ltd."
	replace matchval`x' = 1 if firm_name == "Charmaine Briffa" & winner`x' == "Charmaine Briï¬€a"
	replace matchval`x' = 1 if firm_name == "BBService" & winner`x' == "BBService s.r.l."
	replace matchval`x' = 1 if firm_name == "Perit Mario Cordina" & winner`x' == "Perit Mario Cordina and Perit Angelo Portelli"
	replace matchval`x' = 1 if firm_name == "Zaffarese Signs + Display Ltd" & winner`x' == "Zaï¬€arese Signs + Display Ltd"
	replace matchval`x' = 1 if firm_name == "Ramilene Office Supplies Ltd" & winner`x' == "Ramilene Oï¬ƒce Supplies Ltd"
	replace matchval`x' = 1 if firm_name == "Dr Tiffany Ann Farrugia" & winner`x' == "Dr Tiï¬€any Ann Farrugia"
	replace matchval`x' = 1 if firm_name == "Anthony Mintoff" & winner`x' == "Anthony Mintoï¬€"
	replace matchval`x' = 1 if firm_name == "Medic Sante" & winner`x' == "Medic Sante Co. Ltd."
	replace matchval`x' = 1 if firm_name == "Fgl Information Technology Ltd" & winner`x' == "Forestals Information Technology Limited"
	}



/* Determine winner. In Malta, multiple bids per bidder is allowed in which case which bid (of a given bidder) won is unclear.
Variables winner and winbdi are defined only when which bid won is clear from the data. Bwin and Bwinbid are defined even when
it's unclear. Bwin and Bwinbid should only be used for the puropose of constructing backlog. It should not be used as an outcome variable in an RD analysis.*/

gen winner = .
gen winbid = .
gen Bwin = .
gen Bwinbid = .
forvalues x  = 1/37 {
	replace winner = 1 if matchval`x'>.85 & winbid`x' == bid
	gen LOT`x' = 1 if matchval`x'>.85 & winbid`x' == bid
	replace winbid = winbid`x' if matchval`x'>.85 & winbid`x' == bid
	replace Bwin = 1 if matchval`x'>.85 & winbid`x' == bid
	replace Bwinbid = winbid`x' if matchval`x'>.85 & winbid`x' == bid
}


forvalues x  = 1/37 {
	replace winner`x' = "" if regexm(winner`x', "^__+") == 1
}


* name matches and # of lots is 1 & firm submits only one bid.
cap drop temp
bys id: egen temp = total(winner)
bys id: egen Btemp = total(Bwin)
bys id firm_name: gen temp2 = _N
replace LOT1 = 1 if Lots == "No" & matchval1>.85 & winner == . & temp ==0 & temp2 == 1
gen sa = abs(bid - winbid1)
sort id firm_name sa bid
by id firm_name: gen order = _n
replace Bwin = 1 if Lots == "No" & matchval1>.85 & winner == . & Btemp ==0 & order == 1
replace Bwinbid = winbid1 if Lots == "No" & matchval1>.85 & winner == . & Btemp ==0 & order == 1
replace Bwinbid = bid if Lots == "No" & matchval1>.85 & winner == . & Btemp ==0 & order == 1 & winbid1 == .
replace winbid = winbid1 if Lots == "No" & matchval1>.85 & winner == . & temp ==0 & temp2 == 1
replace winbid = bid if Lots == "No" & matchval1>.85 & winner == . & temp ==0 & temp2 == 1 & winbid1 == .
replace winner =1 if Lots == "No" & matchval1>.85 & winner == . & temp ==0 & temp2 == 1



* name matches and the lots are won by the same firm
gen same = 1
forvalues x  = 2/37 {
	local y = `x'-1
	replace same = 0 if winner`x'~=winner`y' & winner`x'~="" & winner`y'~=""
}
cap drop temp Btemp
bys id: egen temp = total(winner)
bys id: egen Btemp = total(Bwin)
gsort id -matchval1 bid
drop sa order
sort id firm_name bid
by id firm_name: gen order = _n
replace Bwin = 1 if matchval1>.85 & same ==1 & winner == . & Btemp ==0 & order == 1 & Bwin==.
replace Bwinbid = bid if matchval1>.85 & same ==1 & winner == . & Btemp ==0 & order == 1 & Bwinbid==.
replace winbid = bid if matchval1>.85 & same ==1 & winner == . & temp ==0 & temp2 == 1 & winbid==.
replace winner =1 if matchval1>.85 & same ==1 & winner == . & temp ==0 & temp2 == 1


* When Lot name is specified: name matches and firm submits only one bid.
forvalues x  = 1/37 {
	drop temp Btemp
	bys id: egen temp = total(winner)
	bys id: egen Btemp = total(Bwin)
	cap drop lot`x'
	gen lot`x' = 1 if regexm(Envelope, "(L|l)ot `x'") == 1
	replace lot`x' = 1 if regexm(Envelope, "(L|l)ot`x'") == 1
	replace Bwin = 1 if matchval`x'>.85 & Btemp == 0 & lot`x'==1 & order == 1
	replace Bwinbid = winbid`x' if matchval1>.85 & Btemp == 0 & lot`x'==1 & order == 1
	replace Bwinbid = bid if matchval1>.85 & Btemp == 0 & lot`x'==1 & order == 1 & winbid`x' == .
	replace winbid = winbid`x' if matchval`x'>.85 & temp2==1 & temp == 0 & lot`x'==1
	replace winbid = bid if matchval`x'>.85 & temp2==1 & temp == 0 & lot`x'==1 & winbid`x' == .
	replace winner = 1 if matchval`x'>.85 & temp2==1 & temp == 0 & lot`x'==1
	replace LOT`x' = 1 if matchval`x'>.85 & temp2==1 & temp == 0 & lot`x'==1
	drop lot`x'
}

* There are K lots and the winner of K-1 lots are identified:
cap drop temp
bys id: gen temp = 1 if _n == 1 /* set temp = 1 for one entry per id */
bys url_id: egen TotLots = total(temp) /* # of Total Lots per url_id */

forvalues x  = 1/37 { /* There are a total of x lots per url_id */
	bys id: egen id_LOT`x' = max(LOT`x')
	drop LOT`x'
	bys url_id: egen url_idLOT`x' = max(id_LOT`x')
	replace url_idLOT`x' = 0 if url_idLOT`x' == .
}
gen sum_idLOT = 0
forvalues x  = 1/37 { 
	replace sum_idLOT = sum_idLOT + id_LOT`x' if id_LOT`x' ~= .
}
bys url_id: egen morethan1LOTerror = max(sum_idLOT) 

forvalues x  = 2/37 { /* There are a total of x lots per url_id */
	forvalues y = 1/`x' { /* The one missing is the y-th lot */
			cap drop temp Btemp
			bys id: egen temp = total(winner)
			bys id: egen Btemp = total(Bwin)
			cap drop counter
			gen counter = 0
			forvalues z= 1/`x' {
				replace counter = counter + url_idLOT`z' if `z' ~= `y'
			}
			replace Bwin = 1 if matchval`y' >.85 & TotLots == `x' & url_idLOT`y' == 0 & counter == `x'-1 & sum_idLOT == 0 & morethan1LOTerror==1 & Btemp == 0 & order == 1
			replace Bwinbid = winbid`y' if matchval`y' >.85 & TotLots == `x' & url_idLOT`y' == 0 & counter == `x'-1 & sum_idLOT == 0 & morethan1LOTerror==1 & Btemp == 0 & order == 1
			replace Bwinbid = bid if matchval`y' >.85 & TotLots == `x' & url_idLOT`y' == 0 & counter == `x'-1 & sum_idLOT == 0 & morethan1LOTerror==1 & Btemp == 0 & order == 1 & winbid`y' == .
			replace winbid = winbid`y' if matchval`y' >.85 & TotLots == `x' & url_idLOT`y' == 0 & counter == `x'-1 & sum_idLOT == 0 & morethan1LOTerror==1 & temp == 0 & temp2 == 1
			replace winbid = bid if matchval`y' >.85 & TotLots == `x' & url_idLOT`y' == 0 & counter == `x'-1 & sum_idLOT == 0 & morethan1LOTerror==1 & temp == 0 & temp2 == 1 & winbid`y' == .
			replace winner = 1 if matchval`y' >.85 & TotLots == `x' & url_idLOT`y' == 0 & counter == `x'-1 & sum_idLOT == 0 & morethan1LOTerror==1 & temp == 0 & temp2 == 1
	}
}
			
set seed 5555
sort id bid
bys id: egen bid_rank = rank(bid) if bid<., track

* define running variable 
* delta and delta2: relative to the lowest bid
forvalues x = 1(1)3{
	cap drop temp 
	gen temp = bid if bid_rank == `x'
	bys id: egen bid`x' = max(temp)
}
cap drop temp
bys id: egen temp = max(winbid)
gen above_winner = 1 if bid >= temp
sort id above_winner bid
by id above_winner: gen final_rank = _n if above_winner == 1
forvalues x = 1(1)3{
	cap drop temp 
	gen temp = bid if final_rank == `x'
	bys id: egen bid`x'p = max(temp)
}


gen delta = (bid - bid1)/bid1          if bid_rank > 1 
replace delta = (bid - bid2)/bid1      if bid_rank == 1 
	
gen delta2 = (bid - bid2)/bid2          if bid_rank > 2 
replace delta2 = (bid - bid3)/bid2      if bid_rank == 2 


* deltap and delta2p: relative to the winner's bid
gen deltap = (bid - bid1p)/bid1p          if final_rank > 1 & above_winner == 1
replace deltap = (bid - bid2p)/bid1p      if final_rank == 1 & above_winner == 1

gen delta2p = (bid - bid2p)/bid2p          if final_rank > 2 & above_winner == 1
replace delta2p = (bid - bid3p)/bid2p      if final_rank == 2 & above_winner == 1

gen firm_namep = firm_name
replace firm_namep = strlower(firm_namep)
replace firm_namep = regexr(firm_namep,"(co.? )?ltd(\.?)$","")
replace firm_namep = strtrim(firm_namep)
replace firm_namep = regexr(firm_namep,"limited$","")
replace firm_namep = strtrim(firm_namep)

preserve
keep firm_namep
bys firm_namep: gen n = _n
keep if n == 1
strgroup firm_namep, gen(firm_namep_85) thresh(.15) force
save yyy.dta,replace
restore
drop _merge
merge m:1 firm_namep using "yyy.dta"
erase yyy.dta
rename firm_namep_85 firm_id

drop temp
tostring firm_id, gen(temp)
gen firm_date = temp+ "_" + substr(Opening_Date,1,10)

*********************** Updated on 11 May 22; Created on 9 May 22 *************************************************
* This program needs the following 5 variables to be exactly the same name in the data: 
	*1) firm_id; 
	*2) bid_date; 
	*3) winbid; the amount the winning bidder receives. 
		* Specify a variable name as the FIRST argument if you use a customized variable name (added on 11may22);
	*4) winner; assigns 1 for the winner and 0 for others. 
		* Specify a variable name as the SECOND argument if you use a customized variable name (added on 11may22);
	*5) contract_id; numeric
*Note*
*1 Exceptions are 3) and 4). The code accepts a customized varname by specifing the name in the argument
* 
*2 FLAG; the codes ignores observations with FLAG == 1
********************************************************************************************
rename Opening_Date_d bid_date
rename id contract_id
************* backlog construction module *****************
do backlog_construction.do
backlog_construction "Bwinbid" "Bwin"
***********************************************************
************* fowardlog construction module *****************
do forwardlog_construction	
fowardlog_construction "Bwinbid" "Bwin"
************************************************


save "../Data/Malta/malta.dta", replace

